<?php

class OrderController extends BaseController {
	
	public $table = 'order';

	public $model = 'Order';
	
	// Base
	public $base = 'order';
	
	// Localization
	public $local = 'en';
	
	// Language Name
	public $language = 'order';
	
	// Breadcrumbs
	public $breadcrumbs = [ ['link' => 'order' , 'title' => 'Orders'] ];

	/**
	 * Set List Filters
	 *  - Use to create filter and Manipulate Model
	 * @return Null
	 * */
	public function setListData( $data, $object ) {
		$object = $this->_filterData( $data, $object );
		$object = $object->select(
				'order.id', 'order.status', 'order.payment_method', 'order.peso', 'order.points', 'order.delivery_fee',
				'order.tax', 'order.topup AS topup_total', 'order.created_at', 'order.comment', 'login.email AS employee_email', 
				'login.lastname', 'login.firstname', 'login.employee_number', 'company.code AS company_code', 'login.BRANCH_id AS branch_code'
			)
			->whereHas('orderProducts', function( $op ) {
				$op->where('archived', '=', 0);
			})
			->join('company', 'company.id', '=', 'order.COMPANY_id')
			->join('login', 'login.id', '=', 'order.EMPLOYEE_id')
			->join('company_branch', 'login.BRANCH_id', '=', 'company_branch.id')
			->orderBy('id', 'DESC');
			
		return $object;
	}
	
	/**
	 * Additional List Data.
	 * @param $data
	 * @return mixed
	 */
	public function addListData( $data ) {
		$statuses = Constant::getOrderList( true );
		$data['statuses'] = array('all' => 'All Statuses');
		foreach ( $statuses as $key => $val )
			$data['statuses'][$key] = $val;

		// Retrieve Order Items
		$order_ids = $data['results']->lists('id');
		$items = OrderProduct::active()
			->leftJoin( 'category', 'category.id', '=', 'order_product.CATEGORY_id' )
			->leftJoin( 'purchase_order_product', function ( $pop ) {
				$pop->on( 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id' )
					->where( 'purchase_order_product.archived', '=', 0 );
			})
			->leftJoin( 'purchase_order', 'purchase_order_product.PURCHASE_ORDER_id', '=', 'purchase_order.id' )
			->whereIn( 'ORDER_id', empty($order_ids) ? array(0) : $order_ids )
			->get( array(
				'order_product.ORDER_id', 'order_product.id', 'order_product.status', 'order_product.SIZE_id', 'order_product.PRODUCT_id', 'order_product.ORDER_PRODUCT_OPTION_id',
				'order_product.model', 'order_product.quantity', 'order_product.benstore_price', 'order_product.tax', 'order_product.peso',
				'order_product.points', 'order_product.topup', 'order_product.name', 'category.name AS category_name', 'purchase_order.approved_at AS po_date',
				'order_product.delivery_fee_peso', 'order_product.delivery_fee_points'
			) );
		
		// Group Order Items By Order and Their Set
		$results = array();
		foreach ( $items as $item ) {
			$order_id 	= (int) $item->ORDER_id;
			$size_id 	= (int) $item->SIZE_id;
			$item_id	= (int) $item->id;
			$status 		= (int) $item->status;
			$prod_id	= (int) $item->PRODUCT_id;
			$order_prod_option_id = (int) $item->ORDER_PRODUCT_OPTION_id;
			
			// Is new order?
			if ( !array_key_exists($order_id, $results) )
				$results[$order_id] = array('status_info' => array());
			
			// Is first item in Order Set?
			if ( !array_key_exists($size_id, $results[$order_id]) ) {
				$results[$order_id][$size_id] = array(
					'products'					=> array(),
					'total_tax'					=> 0,
					'total_delivery_peso'	=> 0,
					'total_delivery_points'	=> 0,
					'total_peso'				=> 0,
					'total_points'				=> 0,
					'total_qty' 					=> 0,
					'total_topup'				=> 0
				);
				
				// Retrieve Delivery Details
				$delivery = OrderToSet::where(array('ORDER_id'	=> $order_id, 'SET_id'	 => $size_id, 'archived' => 0))->first();
				if ( !empty($delivery) ) {
					$results[$order_id][$size_id]['delivery_details'] = array(
						'fee'			 	=> $delivery->zone_fee,
						'zone_name'	=> $delivery->zone_name,
						'address'	 	=> $delivery->address,
						'method'			=> $delivery->delivery_method,
						'mobile'			=> $delivery->mobile
					);
				}
			}
			
			$results[$order_id][$size_id]['products'][$item_id] = array(
				'name'				=> $item->name,
				'model'				=> $item->model,
				'category'			=> $item->category_name,
				'options'	 			=> array(),
				'benstore_price'	=> $item->benstore_price,
				'quantity'			=> $item->quantity,
				'po_date'			=> ( empty($item->po_date) ) ? '' : Formatter::toDateFormat( $item->po_date, 'm/d/Y' ),
				'delivery_peso'	=> $item->delivery_fee_peso,
				'delivery_points'	=> $item->delivery_fee_points,
				'peso'				=> $item->peso,
				'points'				=> $item->points,
				'tax'					=> $item->tax,
				'topup'				=> $item->topup,
				'status'				=> $status
			);
			
			// Retrive Order Product Options
			$orderOptions = OrderProductOption::where( array(
					'ORDER_PRODUCT_OPTION_id' => $order_prod_option_id,
					'ORDER_id'		=> $order_id,
					'PRODUCT_id'	=> $prod_id
				) )
				->whereNull('archived')
				->orWhere('archived', 0)
				->get();
			if ( $orderOptions->count() ) {
				foreach ( $orderOptions as $option ) {
					// Use Order Item Product quantity if option is only one.
					if ( ($orderOptions->count() === 1) )
						$opt_qty = $item->quantity;
					else
						$opt_qty = ( (int) $option->quantity === 0 ) ? 1 : $option->quantity;
		
					$results[$order_id][$size_id]['products'][$item_id]['options'][$option->id] = array(
						'value' 		=> $option->option_value,
						'price' 		=> $option->price,
						'quantity' 	=> $opt_qty
					);
				}
			}
			
			$results[$order_id][$size_id]['total_tax']		+= $item->tax;
			$results[$order_id][$size_id]['total_peso']		+= $item->peso;
			$results[$order_id][$size_id]['total_points']	+= $item->points;
			$results[$order_id][$size_id]['total_qty'] 		+= $item->quantity;
			$results[$order_id][$size_id]['total_topup'] 	+= $item->topup;
			$results[$order_id][$size_id]['total_delivery_peso'] 	+= $item->delivery_fee_peso;
			$results[$order_id][$size_id]['total_delivery_points'] 	+= $item->delivery_fee_points;
			
			// Status counter
			if ( array_key_exists($status, $results[$order_id]['status_info'] ) )
				$results[$order_id]['status_info'][$status] += 1;
			else
				$results[$order_id]['status_info'][$status] = 1;
			
			ksort($results[$order_id]['status_info']);
		}
		
		$data['items']	= $results;
		$data['sizes'] = Size::active()->lists('name', 'id');
		$data['topup_statuses'] = Constant::getTopupList( true );
		
		$order_comp =  Order::active()->distinct('COMPANY_id')->lists('COMPANY_id');
		$companies = Company::whereIn('id', $order_comp)->orderBy('company.name')->get(array('id', 'name', 'platform_name'));
		
		$data['companies'] = array('All Clients');
		foreach( $companies  as $company ) {
			$company_name = $company->name;
			if ( $company->platform_name )
				$company_name .= ' - ' . $company->platform_name;

			$data['companies'][$company->id] = $company_name;
		}

		return $data;
	}

	/**
	 * Filter List of Data.
	 * 
	 * @author AngelaMisa 03/16/2015 12:00NN
	 * @param $data, $object, $level ( order || item )
	 * @return mixed
	 */
	private function _filterData( $data, $object, $level = 'order' ) {
		// Has filter Order IDs?
		if ( !empty($data['filter_order_ids']) ) {
			$order_ids = preg_split('/\r\n|[\r\n]/', $data['filter_order_ids']);
			$filtered_order_ids = array_filter(array_unique($order_ids));

			if ( !empty($filtered_order_ids) )
				$object = $object->whereIn( 'order.id', $filtered_order_ids );
		}
		
		// Has filter Item IDs?
		if ( !empty($data['filter_item_ids']) ) {
			$item_ids = preg_split('/\r\n|[\r\n]/', $data['filter_item_ids']);
			$filtered_item_ids = array_filter(array_unique($item_ids));
			
			if ( !empty($filtered_item_ids) ) {
				// if item level?
				if ( $level == 'item' ) {
					$object = $object->whereIn( 'order_product.id', $filtered_item_ids );
				} else {
					$object = $object->whereHas('orderProducts', function( $op ) use ( $filtered_item_ids ) {
						$op->whereIn( 'order_product.id', $filtered_item_ids );
					});
				}
			}
		}
		
		// Has filter company?
		if ( !empty($data['filter_company']) )
			$object = $object->where( 'order.COMPANY_id', trim($data['filter_company']) );
		
		// Has filter order status?
		if ( isset($data['filter_status']) && $data['filter_status'] != 'all')
			$object = $object->where('order.status', (int) $data['filter_status']);
		
		// Has filter item status?
		if ( isset($data['filter_item_status']) && $data['filter_item_status'] != 'all') {
			$item_status = (int) $data['filter_item_status'];

			// if item level?
			if ( $level == 'item' ) {
				$object = $object->where( 'order_product.status', '=', $item_status );
			} else {
				$object = $object->whereHas('orderProducts', function( $op ) use ( $item_status ) {
					$op->where( 'order_product.status', '=', $item_status );
				});
			}
		}

		$dt_format = 'm/d/Y H:i:s';
		
		// Has order date start range filter?
		if ( !empty($data['filter_order_date_from']) && trim($data['filter_order_date_from']) ) {
			$from_date = trim($data['filter_order_date_from']) . ' 00:00:00';
			$from_datetime = DateTime::createFromFormat($dt_format, $from_date);
		
			// Is start date format valid?
			if ( $from_datetime && ($from_date == $from_datetime->format($dt_format)) ) {
				$order_start_date = $from_datetime->format('Y-m-d H:i:s');
				$object = $object->where( 'order.created_at', '>=', $order_start_date );
			}
		}
		
		// Has order date end range filter?
		if ( !empty($data['filter_order_date_to']) && trim($data['filter_order_date_to']) ) {
			$to_date = trim($data['filter_order_date_to']) . ' 23:59:59';
			$to_datetime = DateTime::createFromFormat($dt_format, $to_date);
		
			// Is end date format valid?
			if ( $to_datetime && ($to_date == $to_datetime->format($dt_format)) ) {
				$order_end_date = $to_datetime->format('Y-m-d H:i:s');
		
				// Has start date? validate if it's earlier
				if ( (!empty($order_start_date) &&
					($to_datetime > $from_datetime)) || empty($order_start_date) ) {
					$object = $object->where( 'order.created_at', '<=', $order_end_date );
				}
			}
		}
		
		// Has employee number?
		if(!empty($data['filter_employee_id']))
			$object = $object->where('login.employee_number', '=', $data['filter_employee_id']);
		
		// Has employee email?
		if(!empty($data['filter_employee_email']))
			$object = $object->where('login.email', '=', $data['filter_employee_email']);
		
		// Has employee firstname?
		if(!empty($data['filter_employee_firstname']))
			$object = $object->where('login.firstname', 'LIKE','%' . $data['filter_employee_firstname']. '%' );
		
		// Has employee lastname?
		if(!empty($data['filter_employee_lastname']))
			$object = $object->where('login.lastname', 'LIKE', '%' .$data['filter_employee_lastname']. '%');
		
		return $object;
	}
	
	/**
	 * Query filtered active order products.
	 * @author 03/19/2015 8:00PM
	 * @return mixed
	 */
	private function _queryFilteredItems() {
		$items = OrderProduct::leftJoin('order_product_option', function($op) {
				$op->on('order_product.ORDER_id', '=', 'order_product_option.ORDER_id')
					->on('order_product_option.PRODUCT_id', '=', 'order_product.PRODUCT_id')
					->on('order_product_option.ORDER_PRODUCT_OPTION_id', '=', 'order_product.ORDER_PRODUCT_OPTION_id')
					->whereNull('order_product_option.archived')
					->orWhere('order_product_option.archived', '=', 0);
			})
			->leftJoin( 'order_to_set', function ( $order_set ) {
				$order_set->on('order_to_set.ORDER_id', '=', 'order_product.ORDER_id')
					->on('order_to_set.SET_id', '=', 'order_product.SIZE_id')
					->where('order_to_set.archived', '=', 0)
					->where('order_to_set.id', '=', 
						DB::raw('SELECT min(id) from order_to_set where ORDER_id = order_product.ORDER_id AND SET_id = order_product.SIZE_id')
					);
			})
			->leftJoin( 'purchase_order_product', function ( $pop ) {
				$pop->on( 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id' )
				->where( 'purchase_order_product.archived', '=', 0 );
			})
			->leftJoin( 'product_option', function ( $po ) {
				$po->on( 'order_product.PRODUCT_id', '=', 'product_option.PRODUCT_id' )
				->where( 'product_option.archived', '=', 0 );
			})
			->leftJoin( 'purchase_order', 'purchase_order_product.PURCHASE_ORDER_id', '=', 'purchase_order.id' )
			->join( 'size', 'order_product.SIZE_id', '=', 'size.id' )
			->join('order', 'order.id', '=', 'order_product.ORDER_id')
			->join('company', 'company.id', '=', 'order.COMPANY_id')
			->join('login', 'login.id', '=', 'order.EMPLOYEE_id')
			->join('company_branch', 'company_branch.id', '=', 'login.BRANCH_id')
			->join('category', 'category.id', '=', 'order_product.CATEGORY_id')
			->where( array('order.archived' => 0, 'order_product.archived' => 0) )
			->orderBy('order_product.ORDER_id', 'DESC')
			->orderBy('order_product.SIZE_id', 'ASC');
		
		return $this->_filterData( Input::all(), $items, 'item' );
	}
	
	/**
	 * Export List of Order Items based on filters.
	 * @author AngelaMisa 03/20/2015 11:49PM
	 * @param $print
	 * @return void
	 */
	public function exportList( $print = false ) {
		// Get Result Set
		$items = OrderProduct::select(
			array(
				'order.status AS order_status', 'order.created_at AS date_ordered', 'order.comment','order_product.name', 'order_product.model', 'order_product.benstore_price',
				'company.code AS company_code', 'login.lastname', 'login.firstname', 'company_branch.id AS branch_code', 'login.employee_number', 'login.email',
				'order_product.topup', 'order_product.status AS item_status', 'order_product.peso', 'order_product.delivery_fee_peso', 'order_product.delivery_fee_points',
				'order_product.points', 'order_product.tax', 'order_product.ORDER_id', 'order_product.id', 'order_product.quantity', 'order.created_at AS date_ordered',
				'order_product_option.option_value', 'order_product_option.quantity AS option_quantity', 'order_product_option.price AS option_price',
				'order_to_set.zone_fee', 'order_to_set.mobile', 'order_to_set.zip_code', 'order_to_set.unit', 'order_to_set.street', 'order_to_set.barangay',
				'order_to_set.city',  'order_to_set.delivery_method', 'order_to_set.zone_name', 'order_to_set.address',
				'category.name AS category', 'purchase_order.approved_at AS po_date', 'size.name AS size',
				'order.peso AS order_peso', 'order.tax AS order_tax', 'order.points AS order_points', 'order.delivery_fee', 'order.topup AS order_topup'
			))
			->leftJoin('order_product_option', function($op) {
				$op->on('order_product.ORDER_PRODUCT_OPTION_id', '=', 'order_product_option.ORDER_PRODUCT_OPTION_id')
					->on('order_product.ORDER_id', '=', 'order_product_option.ORDER_id')
					->on('order_product.PRODUCT_id', '=', 'order_product_option.PRODUCT_id')
					->whereNull('order_product_option.archived')
					->orWhere('order_product_option.archived', '=', 0);
			})
			->leftJoin( 'order_to_set', function ( $order_set ) {
				$order_set->on('order_to_set.ORDER_id', '=', 'order_product.ORDER_id')
					->on('order_to_set.SET_id', '=', 'order_product.SIZE_id')
					->where('order_to_set.archived', '=', 0)
					->where('order_to_set.id', '=', 
						DB::raw('SELECT min(id) from order_to_set where ORDER_id = order_product.ORDER_id AND SET_id = order_product.SIZE_id')
					);
			})
			->leftJoin( 'purchase_order_product', function ( $pop ) {
				$pop->on( 'order_product.id', '=', 'purchase_order_product.ORDER_PRODUCT_id' )
					->where( 'purchase_order_product.archived', '=', 0 );
			})
			->leftJoin( 'purchase_order', 'purchase_order_product.PURCHASE_ORDER_id', '=', 'purchase_order.id' )
			->join( 'size', 'order_product.SIZE_id', '=', 'size.id' )
			->join('order', 'order.id', '=', 'order_product.ORDER_id')
			->join('company', 'company.id', '=', 'order.COMPANY_id')
			->join('login', 'login.id', '=', 'order.EMPLOYEE_id')
			->join('company_branch', 'company_branch.id', '=', 'login.BRANCH_id')
			->join('category', 'category.id', '=', 'order_product.CATEGORY_id')
			->where( array('order.archived' => 0, 'order_product.archived' => 0) )
			->orderBy('ORDER_id', 'DESC')
			->orderBy('SIZE_id', 'ASC');
		$results =  $this->_filterData( Input::all(), $items, 'item' );
		
		$this->data['export'] = [];
		$results->chunk(500, function( $orders ) {
			$this->data['export'] = array_merge($this->data['export'], $orders->toArray());
		});

		return View::make( 'export.order_list' )->with( array(
			'is_print'		=> $print,
			'items'		=> $this->data['export'],
			'statuses'	=> Constant::getOrderList( true ),
			'headers'	=> array(
				'Sales Order Id', 'Order Status', 'Item Id', 'Item Status', 'Employee ID', 'Firstname', 'Lastname', 'Email', 'Mobile', 'Company', 'Branch', 'Product Name', 'Model', 'Category', 'Size', 'Benstore Price', 'QTY', 'Option Value', 'Option QTY', 'Option Price',
				'Delivery Method', 'Unit', 'Street', 'Barangay', 'City', 'Zip Code', 'Complete Address', 'Delivery Fee Peso', 'Delivery Fee Points', 'Peso', 'Tax', 'Points', 'Top-up', 'Total Order Delivery Fee', 'Total Order Peso', 'Total Order Tax',  'Total Order Points', 'Total Order Top-up',
				'Date Ordered', 'PO Date', 'Date Delivered', 'Date Received', 'Remarks'
			)
		));
	}
	
	/**
	 * Export List of Order Items with columnized options based on filters.
	 * @author AngelaMisa 03/20/2015 11:49PM
	 * @param $print
	 * @return void
	 */
	public function exportOptionList( $print = false ) {
		// Get Available Distinct Order Product Option Titles as Header
		$uniq_prod_ids = $this->_queryFilteredItems()
			->select('order_product.PRODUCT_id')
			->groupBy('order_product_option.PRODUCT_id')
			->lists('order_product.PRODUCT_id');
		$option_titles = ProductOption::active()
			->whereIn( 'PRODUCT_id', $uniq_prod_ids )
			->distinct( 'title' )
			->lists('title', 'id');
		
		$results = $this->_queryFilteredItems()->select( array(
			'order.status AS order_status', 'order.created_at AS date_ordered', 'order.comment','order_product.name', 'order_product.model', 'order_product.benstore_price',
			'company.code AS company_code', 'login.lastname', 'login.firstname', 'company_branch.id AS branch_code', 'login.employee_number', 'login.email',
			'order_product.topup', 'order_product.status AS item_status', 'order_product.peso', 'order_product.delivery_fee_peso', 'order_product.delivery_fee_points',
			'order_product.points', 'order_product.tax', 'order_product.ORDER_id', 'order_product.id', 'order_product.quantity', 'order.created_at AS date_ordered',
			'order_product_option.option_value', 'order_product_option.quantity AS option_quantity', 'order_product_option.price AS option_price',
			'category.name AS category', 'purchase_order.approved_at AS po_date', 'size.name AS size',  'product_option.id AS prod_opt_id'
		) );
			
		$this->data['export'] = [];
		$results->chunk(500, function( $orders ) {
			$this->data['export'] = array_merge($this->data['export'], $orders->toArray());
		});

		$headers = ['Sales Order Id', 'Order Status', 'Item Id', 'Item Status', 'Employee ID', 'Firstname', 'Lastname', 'Email', 'Company', 'Branch', 'Product Name', 'Model', 'Category', 'Size', 'Benstore Price', 'QTY'];
		
		// Prepare Option Columns (Value, Price, QTY)
		$option_cols = array();
		$start = count($headers);
		foreach ( $option_titles as  $key => $val ) {
			$headers[] = $val . ' Option';
			$headers[] = $val . ' Option Price';
			$headers[] = $val . ' Option QTY';
			$option_cols[$start] = $key;
			$start = $start + 2;
		}

		$headers = array_merge( $headers, array('Delivery Fee Peso', 'Delivery Fee Points', 'Peso', 'Tax', 'Points', 'Top-up', 'Comment') );
		
		return View::make( 'export.order_option_list' )->with( array(
			'is_print'			=> $print,
			'items'			=> $this->data['export'],
			'statuses'		=> Constant::getOrderList( true ),
			'headers'		=> $headers,
			'option_cols'	=> $option_cols
		));
	}

	/**
	 * Retrieve preview list.
	 * @author AngelaMisa 03/18/2015 10:00PM
	 * @return mixed
	 */
	public function getPreviewList( $status, $limit = 5 ) {
		$data['payment_method'] = Constant::getPaymentList( true );
		$data['results'] =  Order::active()
			->where('status', (int) $status )
			->orderBy( 'order.created_at', 'DESC')->limit(5)
			->get();
		$data['status'] = Constant::getName( Constant::LIST_ORDER, $status, true );
		
		return View::make( 'order-preview-list' )->with( $data );
	}
}